﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Vtron.LEDCorrective.Infrastructure
{
    public sealed class ExcelExtension
    {
        public static readonly ExcelExtension Instance = new ExcelExtension();

        public DataTable ReadExcel(string fileName)
        {
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties='Excel 12.0;HDR=NO;TypeGuessRows = 10000;IMEX=1;'"))
            {
                try
                {
                    conn.Open();

                    DataTable tblSchema = null;
                    tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    string tableName = tblSchema.Rows[0]["TABLE_NAME"].ToString();

                    OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + tableName.TrimEnd('\'').TrimStart('\'') + "A1:EE1]", conn);
                    DataTable dtContent = new DataTable();
                    da.SelectCommand.CommandText = "select * from [" + tableName.TrimEnd('\'').TrimStart('\'') + "A1:EE]";
                    da.Fill(dtContent);

                    conn.Close();
                    da.Dispose();

                    return dtContent;
                }
                catch
                {
                    throw new Exception("Excel格式不正确，数据必须在第一页！");
                }
            }
        }

        /// <summary>
        /// 将数据写入到Excel文件中
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="ds"></param>
        public void WriteExcel(string fileName, DataSet ds)
        {
            using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + fileName + "; Extended Properties='Excel 12.0;HDR=NO;'"))
            {
                try
                {
                    OleDbCommand objCmd = new OleDbCommand();
                    objCmd.Connection = conn;
                    objCmd.Connection.Open();

                    objCmd.CommandText = @"CREATE TABLE Sheet1(";
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        objCmd.CommandText += i + " varchar,";
                    }
                    objCmd.CommandText=objCmd.CommandText.Substring(0, objCmd.CommandText.Length - 1);
                    objCmd.CommandText += ")";
                    objCmd.ExecuteNonQuery();

                    string insert;
                    insert = "INSERT INTO Sheet1(";
                    for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    {
                        insert += i + " ,";
                    }
                    insert=insert.Substring(0, insert.Length - 1);
                    insert += " VALUES (";
                    for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                    {
                        objCmd.CommandText = insert;

                        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                        {
                            objCmd.CommandText += ds.Tables[0].Rows[j][i] + " ,";
                        }
                        objCmd.CommandText = objCmd.CommandText.Substring(0, objCmd.CommandText.Length - 1);
                        objCmd.CommandText += ")";

                        objCmd.ExecuteNonQuery();
                    }

                    objCmd.Dispose();
                    objCmd.Connection.Close();
                }
                catch
                {
                    throw new Exception("数据写入Excel失败！");
                }
            }
        }
    }
}
